DROP TRIGGER UPDATE_EMPLOYEE_1_ID_TRI;
DROP TRIGGER UPDATE_DEPARTMENT_1_ID_TRI;
DROP SEQUENCE EMPLOYEE_ID_1_SEQ;
DROP SEQUENCE DEPARTMENT_ID_1_SEQ;
DROP TABLE EMPLOYEES_1;
DROP TABLE DEPARTMENTS_1;

--创建表
CREATE TABLE DEPARTMENTS_1(
  DEPARTMENT_ID INT PRIMARY KEY,
  DEPARTMENT_NAME VARCHAR2(20) NOT NULL
);

CREATE TABLE EMPLOYEES_1(
  EMPLOYEE_ID INT PRIMARY KEY,
  EMPLOYEE_NAME VARCHAR2(20) NOT NULL,
  SEX INTEGER NOT NULL CHECK (SEX IN (1,0)),
  TELEPHONE CHAR(11),
  DEPARTMENT_ID INT REFERENCES DEPARTMENTS_1(DEPARTMENT_ID)
);

--创建序列
CREATE SEQUENCE DEPARTMENT_ID_1_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE;

CREATE SEQUENCE EMPLOYEE_ID_1_SEQ
INCREMENT BY 1
START WITH 1
NOMAXVALUE;

--创建触发器
CREATE TRIGGER UPDATE_DEPARTMENT_1_ID_TRI BEFORE
INSERT ON DEPARTMENTS_1 FOR EACH ROW WHEN(NEW.DEPARTMENT_ID IS NULL)
BEGIN
SELECT DEPARTMENT_ID_1_SEQ.NEXTVAL INTO:NEW.DEPARTMENT_ID FROM DUAL;
END;

CREATE TRIGGER UPDATE_EMPLOYEE_1_ID_TRI BEFORE
INSERT ON EMPLOYEES_1 FOR EACH ROW WHEN(NEW.EMPLOYEE_ID IS NULL)
BEGIN
SELECT EMPLOYEE_ID_1_SEQ.NEXTVAL INTO:NEW.EMPLOYEE_ID FROM DUAL;
END;
